-- =============================================
-- This stored procedure should be called at the end of publishing process
-- to update all the necessary fields.
--
-- Modifications
-- 06/12/2003   E.Tatsui   Created
-- 04/16/2007    W.Archer   Updated
-- =============================================

CREATE                        PROCEDURE [dbo].[amsp_CMFinalizePublish]
    @InPublishRequestDetailID int,
  @InPublishedDateTime datetime,
  @InContactID numeric
  AS

BEGIN
  DECLARE
    @ContentID numeric,
    @NavMenuID numeric,
    @PreviousContentID numeric,
    @HTMLContentID numeric,
    @NavContentGroupInd char(1),
    @ParentNavMenuID numeric,
    @DefaultContentID numeric,
    @FilePath varchar(255),
    @FileName varchar(255),
    @WorkflowStatusCode char(1),
    @PublicationDate datetime,
    @DestDirectory varchar(255),
    @NewPreFuseURL varchar(255),
    @NewPostFuseURL varchar(255),
    @OldPreFuseURL varchar(255),
    @OldPostFuseURL varchar(255),
    @NewNavMenuPreFuseURL varchar(255),
    @NewNavMenuPostFuseURL varchar(255),
    @OldNavMenuPreFuseURL varchar(255),
    @OldNavMenuPostFuseURL varchar(255),
    @ExpirationDate datetime,
    @CMResetAllContentExpDate varchar(10),
    @ExpirationDays integer,
    @NavWorkflowStatusCode char(1),
    @ContentCount int,
    @PublishRegenerateInd char(1),
    @PublishRequestID int,
    @PublishedDirectory varchar(255),
    @PublishDirectory varchar(255),
    @NewDefaultContentID numeric,
    @RequestPublishedDateTime datetime,
    @RequestNavMenuID numeric,
    @WebsiteKey uniqueidentifier

  SELECT @ContentID = a.ContentID,
         @PreviousContentID = a.PreviousContentID,
         @HTMLContentID = b.ContentID,
         @OldPreFuseURL = a.PreFuseURL,
         @OldPostFuseURL = a.PostFuseURL,
         @WorkflowStatusCode = a.WorkflowStatusCode,
         @PublicationDate = a.PublicationDate,
         @ExpirationDate = a.ExpirationDate,
         @ExpirationDays = a.ExpirationDays,
         @NavContentGroupInd = c.NavContentGroupInd,
         @FilePath = c.FilePath,
         @NavMenuID = c.NavMenuID,
         @ParentNavMenuID = c.ParentNavMenuID,
         @DefaultContentID = c.ContentID,
         @OldNavMenuPreFuseURL = c.PreFuseURL,
         @OldNavMenuPostFuseURL = c.PostFuseURL,
         @NavWorkflowStatusCode = c.WorkflowStatusCode,
         @PublishRegenerateInd = d.PublishRegenerateInd,
         @PublishRequestID = d.PublishRequestID,
         @PublishedDirectory = c.PublishedDirectory,
         @PublishDirectory = Replace(CASE WHEN c.NavContentGroupInd = 'N' THEN IsNull(e.NavPublishDirectory,'')
                                          ELSE IsNull(e.ContentFolderPublishDirectory,'') END
                                     + c.FilePath,'\','/'),
         @WebsiteKey = e.WebsiteKey,
         @RequestPublishedDateTime = f.PublishedDateTime,
         @RequestNavMenuID = f.NavMenuID
    FROM Content a WITH (NOLOCK) LEFT OUTER JOIN Content_HTML b WITH (NOLOCK)
      ON a.ContentID = b.ContentID, Nav_Menu c WITH (NOLOCK), Publish_Request_Detail d WITH (NOLOCK),
         Website e WITH (NOLOCK), Publish_Request f WITH (NOLOCK)
   WHERE a.NavMenuID = c.NavMenuID
     AND a.ContentID = d.ContentID
     AND c.WebsiteKey = e.WebsiteKey
     AND d.PublishRequestDetailID = @InPublishRequestDetailID
     AND d.PublishRequestID = f.PublishRequestID

  -- Take care of Publish_Request first.
  IF @RequestPublishedDateTime IS NULL BEGIN
    UPDATE Publish_Request
       SET PublishedDateTime = @InPublishedDateTime
     WHERE PublishRequestID = @PublishRequestID
  END

  -- If we published the content (instead of deleting them).
  IF @WorkflowStatusCode = 'A' OR @WorkflowStatusCode = 'P' BEGIN
    SELECT @CMResetAllContentExpDate = Value
      FROM System_Variable
     WHERE Name = 'CMResetAllContentExpDate'
  
    -- Archive the previous content if there is one.
    IF @PreviousContentID IS NOT NULL AND @WorkflowStatusCode = 'A' BEGIN
      UPDATE Content
         SET WorkflowStatusCode = 'R'
       WHERE ContentID = @PreviousContentID
  
      INSERT INTO Content_Workflow_Log (
             ContentID,
             WorkflowStatusCode,
             ContactID,
             ChangeDateTime)
      VALUES (
             @PreviousContentID,
             'R',
             @InContactID,
             CURRENT_TIMESTAMP)
    END  -- Ends archiving
    
    -- Are we resetting expiration date?
    -- CMResetAllContentExpDate determines whether to reset expiration date when Nav_Menu is published.
    -- If set to True, a content record will have a new expiration date every time it is published.
    -- If set to False, the content record will have a new expiration date only when the content has actually been
    -- modified.

    IF (@WorkflowStatusCode = 'A' OR UPPER(@CMResetAllContentExpDate) = 'TRUE') AND @ExpirationDays IS NOT NULL AND @ExpirationDays > 0
      SET @ExpirationDate = DateAdd(dd,@ExpirationDays,CURRENT_TIMESTAMP)
    ELSE IF (@WorkflowStatusCode = 'A' OR UPPER(@CMResetAllContentExpDate) = 'TRUE')
            AND (@ExpirationDays IS NULL OR @ExpirationDays = 0)
      SET @ExpirationDate = NULL

    -- Set PublicationDate only if it's null.
    IF @PublicationDate IS NULL
      SET @PublicationDate = @InPublishedDateTime

    UPDATE Content
       SET WorkflowStatusCode = 'P',
           PublishDateTime = @InPublishedDateTime,
           ReminderSentDateTime = NULL,
           ExpirationDate = @ExpirationDate,
           PublicationDate = @PublicationDate,
           ContactID = @InContactID
     WHERE ContentID = @ContentID
  
    INSERT INTO Content_Workflow_Log (ContentID,
                                      WorkflowStatusCode,
                                      ContactID,
                                      ChangeDateTime)
         VALUES (@ContentID,
                 'P',
                 @InContactID,
                 CURRENT_TIMESTAMP)
  
    -- Update Pre & PostFuseURL if necessary.
    EXECUTE amsp_CMGetFuseURL @NavMenuID, @ContentID, @NewPreFuseURL OUTPUT, @NewPostFuseURL OUTPUT
    
    -- If PostFuseURL changes, update it.
    IF IsNull(@NewPreFuseURL,'') <> IsNull(@OldPreFuseURL,'')
       OR IsNull(@NewPostFuseURL,'') <> IsNull(@OldPostFuseURL,'') BEGIN
      UPDATE Content
         SET PostFuseURL = @NewPostFuseURL
       WHERE ContentID = @ContentID
    END
    
    -- Default Content?
    IF @PreviousContentID = @DefaultContentID BEGIN
      SET @DefaultContentID = @ContentID
      UPDATE Nav_Menu
         SET ContentID = @ContentID
       WHERE NavMenuID = @NavMenuID
    END

  END -- When content's status is A or P.
  -- If Content's workflow status was "D"
  ELSE IF @WorkflowStatusCode = 'D' BEGIN

    -- Move the content to recycle bin. Change the ID to recycle bin's ID when Nav Item is deleted.
    UPDATE Content
       SET WorkflowStatusCode = 'Y',
           NavMenuID = CASE WHEN @NavWorkflowStatusCode = 'D' THEN (SELECT NavMenuID FROM Nav_Menu WHERE Name = 'Content_Recycle_Bin')
                       ELSE NavMenuID END
     WHERE ContentID = @ContentID

    INSERT
      INTO Content_Workflow_Log (
           ContentID,
           WorkflowStatusCode,
           ContactID,
           ChangeDateTime)
    VALUES (@ContentID,
           'Y',
            @InContactID,
            CURRENT_TIMESTAMP)
  END

  -- If NavMenu is marked for deletion, find out if there are more content associated with this nav item.
  IF @NavWorkflowStatusCode = 'D' BEGIN

    SELECT @ContentCount = Count(*)
      FROM Content
     WHERE NavMenuID = @NavMenuID

    IF @ContentCount = 0 BEGIN
      EXECUTE amsp_CMRequestPublish @NavMenuID, NULL, @InContactID, 'N', 'Y', 'R', DEFAULT, @PublishRequestID, NULL

      UPDATE Publish_Request
         SET NavMenuID = NULL
       WHERE NavMenuID = @NavMenuID

      -- Delete all the related records.
      DELETE FROM Nav_Menu_Workflow_Log WHERE NavMenuID = @NavMenuID
      DELETE FROM Nav_Menu_Feature WHERE NavMenuID = @NavMenuID
      DELETE FROM Nav_Menu_Security_Group WHERE NavMenuID = @NavMenuID
      DELETE FROM Nav_Menu WHERE NavMenuID = @NavMenuID
    END
  END
  -- Not marked for deletion. For default content only.
  ELSE IF @DefaultContentID = @ContentID BEGIN
    -- Update Nav_Menu table.
    EXECUTE amsp_CMGetFuseURL @NavMenuID, NULL, @NewNavMenuPreFuseURL OUTPUT, @NewNavMenuPostFuseURL OUTPUT
    -- If this is the default content, also update Nav_Menu table.
    IF IsNull(@NewNavMenuPreFuseURL,'')  <> IsNull(@OldNavMenuPreFuseURL,'')
       OR IsNull(@NewNavMenuPostFuseURL,'')  <> IsNull(@OldNavMenuPostFuseURL,'') BEGIN
      UPDATE Nav_Menu
         SET PreFuseURL = @NewNavMenuPreFuseURL,
             PostFuseURL = @NewNavMenuPostFuseURL
       WHERE NavMenuID = @NavMenuID
    END

    -- Store the new publish path.
    IF IsNull(@PublishedDirectory,'') <> @PublishDirectory
      UPDATE Nav_Menu
         SET PublishedDirectory =  @PublishDirectory
       WHERE NavMenuID = @NavMenuID

    -- Regenererate navigations if this is default content record for a nav item.
    IF @PublishRegenerateInd = 'P' AND @NavContentGroupInd = 'N'
      EXECUTE amsp_CMRequestPublish @NavMenuID, NULL, @InContactID, 'N', 'Y', 'R', DEFAULT, @PublishRequestID, NULL
  END

END

GO